﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;
using KPIS.GERP.GIMS.IDAL;
using System.IO;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class DocumentDAL : IDALDocument
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DocumentDAL()
        {

        }

        public IList<DocumentInfo> GetDocument(int seq, string table_name, string field_name, string pk_field)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<DocumentInfo> infoList = new List<DocumentInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT " + pk_field
                        + ", FILE_NAME"
                        + ", FILE_EXTENSION"
                        + ", FILE_TYPE"
                        + " FROM " + table_name
                        + " WHERE " + field_name + " = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DocumentInfo info = new DocumentInfo();
                        info.Seq = Convert.ToInt32(dr[i][pk_field]);
                        info.Name = dr[i]["FILE_NAME"].ToString();
                        info.Extension = dr[i]["FILE_EXTENSION"].ToString();
                        info.Content_type = dr[i]["FILE_TYPE"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void InsertFileToDB(DocumentInfo info, string table_name, string fk_field, int seq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@name", info.Name);
                dbManager.AddParameters(2, "@file_type", ConvertExtensionToContentType(info.File_info.Extension));
                dbManager.AddParameters(3, "@file_extension", info.File_info.Extension);
                dbManager.AddParameters(4, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(5, "@created_when", info.InfoRecord.Created_when);

                strSQL = "INSERT INTO " + table_name
                + " (" + fk_field + ", FILE_NAME, FILE_TYPE, FILE_EXTENSION"
                + " , CREATED_BY, CREATED_WHEN)"
                + " VALUES(@seq"
                + ", @name"
                + ", @file_type"
                + ", @file_extension"
                + ", @created_by"
                + ", @created_when"
                + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                int lastSeq = 0;
                DataSet ds;
                strSQL = "SELECT LAST_INSERT_ID()"
                        + " FROM " + table_name;

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                string baseLocation = @ConfigurationManager.AppSettings["PrePurchaseDocPath"].ToString();
                if (!System.IO.Directory.Exists(baseLocation))
                    System.IO.Directory.CreateDirectory(baseLocation);

                if (System.IO.File.Exists(baseLocation + lastSeq.ToString() + info.File_info.Extension))
                {
                    System.IO.File.Delete(baseLocation + lastSeq.ToString() + info.File_info.Extension);
                }
                info.File_info.CopyTo(baseLocation + lastSeq.ToString() + info.File_info.Extension, true);
                info.Dest_path = baseLocation + lastSeq.ToString() + info.File_info.Extension;
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertFileToDB(IList<DocumentInfo> infoList, string table_name, string fk_field, int seq, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManagerTrans.CreateParameters(6);

                    dbManagerTrans.AddParameters(0, "@seq", seq);
                    dbManagerTrans.AddParameters(1, "@name", infoList[i].Name);
                    dbManagerTrans.AddParameters(2, "@file_type", ConvertExtensionToContentType(infoList[i].File_info.Extension));
                    dbManagerTrans.AddParameters(3, "@file_extension", infoList[i].File_info.Extension);
                    dbManagerTrans.AddParameters(4, "@created_by", infoList[i].InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(5, "@created_when", infoList[i].InfoRecord.Created_when);

                    strSQL = "INSERT INTO " + table_name
                    + " (" + fk_field + ", FILE_NAME, FILE_TYPE, FILE_EXTENSION"
                    + " , CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@seq"
                    + ", @name"
                    + ", @file_type"
                    + ", @file_extension"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);

                    int lastSeq = 0;
                    DataSet ds;
                    strSQL = "SELECT LAST_INSERT_ID()"
                        + " FROM " + table_name;
                    ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    }

                    string baseLocation = @ConfigurationManager.AppSettings["PrePurchaseDocPath"].ToString();
                    if (!System.IO.Directory.Exists(baseLocation))
                    {
                        System.IO.Directory.CreateDirectory(baseLocation);
                    }
                    if (System.IO.File.Exists(baseLocation + lastSeq.ToString() + infoList[i].File_info.Extension))
                    {
                        System.IO.File.Delete(baseLocation + lastSeq.ToString() + infoList[i].File_info.Extension);
                    }
                    infoList[i].File_info.CopyTo(baseLocation + lastSeq.ToString() + infoList[i].File_info.Extension);
                    infoList[i].Dest_path = baseLocation + lastSeq.ToString() + infoList[i].File_info.Extension;

                    dbManagerTrans.CreateParameters(2);
                    dbManagerTrans.AddParameters(0, "@seq", lastSeq);
                    dbManagerTrans.AddParameters(1, "@file_path", infoList[i].Dest_path);
                    strSQL = "UPDATE " + table_name + " SET FILE_PATH = @file_path WHERE " + fk_field + " = @seq";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public void DeleteFileFromDB(IList<DocumentInfo> infoList, string table_name, string pk_field)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();

                strSQL = "DELETE FROM " + table_name
                    + " WHERE " + pk_field + "  IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public static byte[] StrBinaryBase64FromFile(string strFullFileName)
        {
            FileStream fs = new FileStream(strFullFileName, FileMode.Open, FileAccess.Read);
            BinaryReader br = new BinaryReader(fs);
            byte[] abBuffer = br.ReadBytes((int)fs.Length);
            br.Close();
            fs.Close();

            return abBuffer;
        }

        private string ConvertExtensionToContentType(string extension)
        {
            string content_type = "";
            switch (extension.ToLower())
            {
                case ".htm":
                case ".html":
                    content_type = "text/HTML";
                    break;

                case ".doc":
                case ".rtf":
                    content_type = "application/msword";
                    break;

                case ".xls":
                    content_type = "application/vnd.ms-excel";
                    break;

                case ".ppt":
                case ".pps":
                    content_type = "application/vnd.ms-powerpoint";
                    break;

                case ".docx":
                    content_type = "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
                    break;

                case ".xlsx":
                    content_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    break;

                case ".pdf":
                    content_type = "application/pdf";
                    break;
            }
            return content_type;
        }

        public string CreateDocumentFileFromDB(string table_name, string pk_field, string seq, string strFullFileName)
        {
            DataSet ds;
            string strSQL;
            string strFileType = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT FILE_BINARY, FILE_TYPE"
                    + " FROM " + table_name
                    + " WHERE " + pk_field + " = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    if (ds.Tables[0].Rows[0]["FILE_BINARY"].ToString().Length > 0)
                    {
                        strFileType = ds.Tables[0].Rows[0]["FILE_TYPE"].ToString();
                        FileInfo fi = new FileInfo(strFullFileName);
                        string strDirPath = fi.DirectoryName;

                        if (!Directory.Exists(strDirPath))
                        {
                            Directory.CreateDirectory(strDirPath);
                        }

                        FileStream fs = new FileStream(strFullFileName, FileMode.Create, FileAccess.Write);
                        BinaryWriter bw = new BinaryWriter(fs);
                        bw.Write((byte[])ds.Tables[0].Rows[0]["FILE_BINARY"]);
                        bw.Flush();
                        bw.Close();
                        fs.Close();
                    }
                }

            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strFileType;
        }

        public IList<DocumentInfo> GetDocumentHistory(int PrSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<DocumentInfo> infoList = new List<DocumentInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", PrSeq);

                strSQL = "select * " +
                        " from icm_pre_purchase_request_file" +
                        " where icm_ppr_seq in " +
                        " (select icm_ppr_seq from icm_pre_purchase_request_item" +
                        " where icm_pr_item_seq in " +
                        "	(select icm_pr_item_seq from icm_purchase_request where icm_pr_seq = @seq)" +
                        ")";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DocumentInfo info = new DocumentInfo();
                        info.Seq = Convert.ToInt32(dr[i]["icm_ppr_file_seq"]);
                        info.Name = dr[i]["FILE_NAME"].ToString();
                        info.Extension = dr[i]["FILE_EXTENSION"].ToString();
                        info.Content_type = dr[i]["FILE_TYPE"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<DocumentInfo> GetDocumentPprHistory(int PoSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<DocumentInfo> infoList = new List<DocumentInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@icm_po_seq", PoSeq);

                strSQL = "select * " +
                        " from icm_pre_purchase_request_file  " +
                        " where icm_ppr_seq in  " +
                        " (select icm_ppr_seq  " +
                        "	from icm_pre_purchase_request_item " +
                        "	where icm_pr_item_seq in  " +
                        "	(select pri.icm_pr_item_seq  " +
                        "	from icm_purchase_order po " +
                        "	inner join icm_bidder ib on po.icm_bidder_seq = ib.icm_bidder_seq  " +
                        "	inner join icm_purchase_request_item pri on ib.icm_pr_seq = pri.icm_pr_seq " +
                        "	where po.icm_po_seq = @icm_po_seq)" + 
                        ")";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DocumentInfo info = new DocumentInfo();
                        info.Seq = Convert.ToInt32(dr[i]["icm_ppr_file_seq"]);
                        info.Name = dr[i]["FILE_NAME"].ToString();
                        info.Extension = dr[i]["FILE_EXTENSION"].ToString();
                        info.Content_type = dr[i]["FILE_TYPE"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<DocumentInfo> GetDocumentPrHistory(int PoSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<DocumentInfo> infoList = new List<DocumentInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@icm_po_seq", PoSeq);

                strSQL = "select * " +
                        " from icm_purchase_request_file  " +
                        " where icm_pr_seq in  " +
                        " (select pr.icm_pr_seq  " +
                        "	from icm_purchase_order po " +
                        "	inner join icm_bidder ib on po.icm_bidder_seq = ib.icm_bidder_seq  " +
                        "	inner join icm_purchase_request pr on ib.icm_pr_seq = pr.icm_pr_seq " +
                        "	where po.icm_po_seq = @icm_po_seq)";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DocumentInfo info = new DocumentInfo();
                        info.Seq = Convert.ToInt32(dr[i]["icm_ppr_file_seq"]);
                        info.Name = dr[i]["FILE_NAME"].ToString();
                        info.Extension = dr[i]["FILE_EXTENSION"].ToString();
                        info.Content_type = dr[i]["FILE_TYPE"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

    }
}
